# Note: this executes in the global scope

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer
import sqlalchemy

engine=sqlalchemy.create_engine('mysql://localhost/test')
debug=False # set this to true and only the generated code is printed.

MYSQL_TO_ALCHEMY_TYPE = dict(
    char='String',
    int='Integer')

Base = declarative_base()
CLASS_DEF =\
"""
class %s(Base):
\t__tablename__ = '%s'
"""
for tablename in engine.table_names():
    cls_name = tablename.capitalize()
    definition = CLASS_DEF % (cls_name, tablename)
    schema = engine.execute('DESCRIBE %s' % tablename)
    for fname, mtype, null, key, default, extra in schema:
        mlen = int(mtype[mtype.index('(')+1:mtype.index(')')])
        definition += "\t%s=Column(%s(%d), primary_key=%s, nullable=%s, default=%s)\n" % (
            fname, MYSQL_TO_ALCHEMY_TYPE[mtype[:mtype.index('(')]],
            mlen, True if key == 'PRI' else False,
            False if null == 'NO' else True, default)
    if debug:
        print definition
    else:
        exec(definition)
Base.metadata.create_all(engine)




#SHOW_DESCRIBE = r"^\s*\|\s*(?P<field>[a-zA-z0-9]+)\s*\|\s*"\
#                r"(?P<type>[a-zA-Z0-9]+)\((?P<len>[0-9]+)\)\s*\|\s*"\
#                r"(?P<null> NO|YES)\s*\|\s*(?P<key>[0-9a-zA-Z]*)\s*\|\s*"\
#                r"(?P<default>[0-9a-zA-Z]+)\s*\|\s*(?P<extra> [0-9a-zA-Z]*)\s*\|$"


